﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using DevExpress.XtraGrid.Views.Grid;

namespace Print_UI
{
    public partial class FrmImportStudentITComment : CommonForm
    {
        public FrmImportStudentITComment()
        {
            InitializeComponent();
        }
        DataTable tbExcelFile = new DataTable();
        DataTable tbstudent = new DataTable();
        DataTable tbsubject = new DataTable();
        DataTable tbScore = new DataTable();
        public int iDepart, iYearschool, iSemesterID, iResulf;
        private SqlConnection _cnn;
        public SqlConnection Cnn
        {
            get { return _cnn; }
            set { _cnn = value; }
        }
        private void LoadClass()
        {
            var Class = Model.CAT_Class.Where(c => c.DepartmentID == DepartmentID).OrderByDescending(c => c.YearID);

            Dictionary<int, string> dic = new Dictionary<int, string>();
            dic.Add(-1, "----Chọn lớp----");
            foreach (var item in Class)
            {
                dic.Add(item.ID, item.Name);
            }
            cboClass.Properties.ValueMember = "Key";
            cboClass.Properties.DisplayMember = "Value";
            cboClass.Properties.DataSource = dic;
            cboClass.EditValue = -1;

        }
        private void Endable()
        {
            int iClassID = Convert.ToInt32(cboClass.EditValue);
            int iSubjectID = Convert.ToInt32(cboSubject.EditValue);
            if (iClassID != -1 && iSubjectID != -1)
            {
                btnChooseFile.Enabled = true;
                btnImport.Enabled = true;
            }
            else
            {
                btnChooseFile.Enabled = false;
                btnImport.Enabled = false;
            }
        }
        private void LoadSubject()
        {
            int iClassID = Convert.ToInt32(cboClass.EditValue);
            var Subject = Model.PRI_SubjectSetting.Where(c => c.DepartmentID == DepartmentID && c.ClassID == iClassID).OrderByDescending(c => c.YearID).GroupBy(c => c.SubjectID).Select(g => g.FirstOrDefault());
            Dictionary<int, string> dic = new Dictionary<int, string>();
            dic.Add(-1, "----Chọn môn học----");
            foreach (var item in Subject)
            {
                dic.Add(item.SubjectID, item.PRI_Subjects.SubjectName);
            }
            cboSubject.Properties.ValueMember = "Key";
            cboSubject.Properties.DisplayMember = "Value";
            cboSubject.Properties.DataSource = dic;
            cboSubject.EditValue = -1;
        }
        private void FrmImportStudentITComment_Load(object sender, EventArgs e)
        {
            try
            {
                tbExcelFile.Columns.Add("STT", typeof(string));
                tbExcelFile.Columns.Add("Check", typeof(bool));
                tbExcelFile.Columns.Add("Note", typeof(string));
                tbExcelFile.Columns.Add("StudentCode", typeof(string));
                tbExcelFile.Columns.Add("DayofBird", typeof(string));
                tbExcelFile.Columns.Add("StudentFName", typeof(string));
                tbExcelFile.Columns.Add("StudentLName", typeof(string));
                tbExcelFile.Columns.Add("StudentID", typeof(int));
                tbExcelFile.Columns.Add("Remark", typeof(string));

                // GetDataFromBD(); get data from Student table follow Department conditions
                Cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["Cnn"].ConnectionString);
                SqlDataAdapter DaStudent = new SqlDataAdapter();
                DaStudent.SelectCommand = new SqlCommand("SELECT PRI_Student.Code as StudentCode,PRI_Student.ID from PRI_Student where DepartmentID = @DepartmentID", Cnn);
                DaStudent.SelectCommand.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = iDepart;
                DaStudent.Fill(tbstudent);
                // get data from Student table follow Department,Semesters,Year conditions
                SqlDataAdapter DaScore = new SqlDataAdapter();
                DaScore.SelectCommand = new SqlCommand("select PRI_StudentInSubject.StudentID ,PRI_StudentInSubject.ClassID,PRI_StudentInSubject.SubjectID from PRI_StudentInSubject where DepartmentID=@DepartmentID and YearID = @YearID and SemesterID=@SemesterID", Cnn);
                DaScore.SelectCommand.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = iDepart;
                DaScore.SelectCommand.Parameters.Add("@SemesterID", SqlDbType.Int).Value = iSemesterID;
                DaScore.SelectCommand.Parameters.Add("@YearID", SqlDbType.Int).Value = iYearschool;
                DaScore.Fill(tbScore);
                LoadClass();
                LoadSubject();
                btnChooseFile.Enabled = false;
                btnImport.Enabled = false;

            }


            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void btnChooseFile_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            try
            {
                // browsing file
                OpenFileDialog opf = new OpenFileDialog();
                if (opf.ShowDialog() == DialogResult.OK)
                {
                    string sFilePath = opf.FileName;
                    HSSFWorkbook workbook = new HSSFWorkbook((new FileStream(sFilePath, FileMode.Open, FileAccess.Read)));
                    ISheet sheet = workbook.GetSheetAt(0);
                    tbExcelFile.Rows.Clear();
                    if (sheet != null)
                    {

                        // excute loop to get data from excel file by using NPOI references
                        for (int row = 9; row <= sheet.LastRowNum; row++)
                        {

                            if (sheet.GetRow(row) != null)
                            {

                                string sSTT = string.Empty;
                                if (sheet.GetRow(row).GetCell(0) != null)
                                {
                                    sheet.GetRow(row).GetCell(0).SetCellType(CellType.String);
                                    if (sheet.GetRow(row).GetCell(0).StringCellValue.ToString() != string.Empty)
                                    {
                                        sSTT = sheet.GetRow(row).GetCell(0).StringCellValue;
                                    }
                                }

                                string sStudentCode = string.Empty;
                                if (sheet.GetRow(row).GetCell(1) != null)
                                {
                                    sheet.GetRow(row).GetCell(1).SetCellType(CellType.String);
                                    if (sheet.GetRow(row).GetCell(1).StringCellValue.ToString() != string.Empty)
                                    {
                                        sStudentCode = sheet.GetRow(row).GetCell(1).StringCellValue;
                                    }
                                }

                                string sStudentFname = string.Empty;
                                if (sheet.GetRow(row).GetCell(3) != null)
                                {
                                    sheet.GetRow(row).GetCell(2).SetCellType(CellType.String);
                                    if (sheet.GetRow(row).GetCell(3).StringCellValue.ToString() != string.Empty)
                                    {
                                        sStudentFname = sheet.GetRow(row).GetCell(3).StringCellValue;
                                    }
                                }
                                string sStudentLname = string.Empty;
                                if (sheet.GetRow(row).GetCell(4) != null)
                                {
                                    sheet.GetRow(row).GetCell(4).SetCellType(CellType.String);
                                    if (sheet.GetRow(row).GetCell(4).StringCellValue.ToString() != string.Empty)
                                    {
                                        sStudentLname = sheet.GetRow(row).GetCell(4).StringCellValue;
                                    }
                                }
                                string sDayOfbird = string.Empty;
                                if (sheet.GetRow(row).GetCell(5) != null)
                                {
                                    sheet.GetRow(row).GetCell(5).SetCellType(CellType.String);
                                    if (sheet.GetRow(row).GetCell(5).StringCellValue.ToString() != string.Empty)
                                    {
                                        sDayOfbird = sheet.GetRow(row).GetCell(5).StringCellValue;
                                    }
                                }
                                string sRemark = string.Empty;
                                if (sheet.GetRow(row).GetCell(6) != null)
                                {
                                    sheet.GetRow(row).GetCell(6).SetCellType(CellType.String);
                                    if (sheet.GetRow(row).GetCell(6).StringCellValue.ToString() != string.Empty)
                                    {
                                        sRemark = sheet.GetRow(row).GetCell(6).StringCellValue;
                                    }
                                }

                                DataRow drcheck = tbExcelFile.NewRow();

                                //Add Datarow for Excel datatable.

                                drcheck["Note"] = string.Empty;
                                drcheck["Check"] = false;
                                drcheck["StudentCode"] = sStudentCode;
                                drcheck["StudentFName"] = sStudentFname;
                                drcheck["StudentLName"] = sStudentLname;
                                drcheck["DayofBird"] = sDayOfbird;
                                drcheck["STT"] = sSTT;
                                drcheck["Remark"] = sRemark;

                                if (sStudentCode != string.Empty)
                                {
                                    //Assign values into datarow on table StudentTAB
                                    int iStudentID = -1;
                                    int iClassID = Convert.ToInt32(cboClass.EditValue);
                                    int iSubjectID = Convert.ToInt32(cboSubject.EditValue);
                                    // Select query on datatable for comparing with database Student.
                                    string expression = "StudentCode = '" + sStudentCode + "'";
                                    DataRow[] foundRowsStudent;
                                    foundRowsStudent = tbstudent.Select(expression);
                                    if (foundRowsStudent.Length > 0)
                                    {
                                        iStudentID = Convert.ToInt32(foundRowsStudent[0]["ID"]);

                                    }

                                    drcheck["StudentID"] = iStudentID;


                                    if (iStudentID != -1)
                                    {
                                        string expression3 = "StudentID = '" + iStudentID + "' AND ClassID = '" + iClassID + "' AND SubjectID = '" + iSubjectID + "' ";
                                        DataRow[] foundRowsSCore;
                                        foundRowsSCore = tbScore.Select(expression3);
                                        if (foundRowsSCore.Length > 0)
                                        {
                                            drcheck["Check"] = true;

                                        }
                                        else
                                        {
                                            drcheck["Check"] = false;
                                            drcheck["Note"] = "Sinh viên chưa có điểm";
                                        }
                                    }
                                    else if (iStudentID == -1)
                                    {
                                        drcheck["Check"] = false;
                                        drcheck["Note"] = "Sinh viên không tồn tại";
                                    }

                                }
                                else
                                {
                                    drcheck["Check"] = false;
                                    drcheck["Note"] = "Thiếu mã số sinh viên";

                                }
                                tbExcelFile.Rows.Add(drcheck);


                            }

                        }
                        grdimportdata.DataSource = tbExcelFile;

                    }
                }

            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }
        }
        private void btnImport_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            try
            {
                Cnn.Open();
                if (gridView1.RowCount == 0)
                {
                    MessageBox.Show("Không có dữ liệu", "Vui lòng kiểm tra lại", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
                }
                else
                {

                    string sSelect = "Check= 1";
                    DataRow[] drRow = tbExcelFile.Select(sSelect);
                    if (drRow.Length == 0)
                    {
                        MessageBox.Show("Dữ liệu không hợp lệ,Vui lòng kiểm tra lại", "Cảnh báo", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
                    }

                    else if (drRow.Length > 0)
                    {
                        int iClassID = Convert.ToInt32(cboClass.EditValue);
                        int iSubjectID = Convert.ToInt32(cboSubject.EditValue);
                        // using loop to update schoolfee
                        for (int irow = 0; irow < drRow.Length; irow++)
                        {

                            // update schoofe and FeeDebit folow currently semester and year
                            SqlCommand cmmUpdateStundentInSubject = new SqlCommand("Update  PRI_StudentInSubject Set Remarks =@Remarks,ModifiedDate=@ModifiedDate,ModifiedBy=@ModifiedBy Where StudentID=@StudentID and ClassID =@ClassID and SubjectID=@SubjectID and YearID = @YearID and SemesterID=@SemesterID ", Cnn);
                            cmmUpdateStundentInSubject.Parameters.Add("@StudentID", SqlDbType.Int).Value = Convert.ToInt32(drRow[irow]["StudentID"]);
                            cmmUpdateStundentInSubject.Parameters.Add("@YearID", SqlDbType.Int).Value = iYearschool;
                            cmmUpdateStundentInSubject.Parameters.Add("@SemesterID", SqlDbType.Int).Value = iSemesterID;
                            cmmUpdateStundentInSubject.Parameters.Add("@ClassID", SqlDbType.Int).Value = iClassID;
                            cmmUpdateStundentInSubject.Parameters.Add("@SubjectID", SqlDbType.Int).Value = iSubjectID;
                            cmmUpdateStundentInSubject.Parameters.Add("@Remarks", SqlDbType.NVarChar).Value = Convert.ToString(drRow[irow]["Remark"]);
                            //cmmUpdateStundentInSemester.Parameters.Add("@SchoolFee", SqlDbType.Money).Value = Convert.ToDecimal(query);
                            cmmUpdateStundentInSubject.Parameters.Add("@ModifiedDate", SqlDbType.DateTime).Value = DateTime.Now;
                            cmmUpdateStundentInSubject.Parameters.Add("@ModifiedBy", SqlDbType.NVarChar).Value = UserName;
                            iResulf = cmmUpdateStundentInSubject.ExecuteNonQuery();

                        }
                        if (iResulf > 0)
                        {
                            MessageBox.Show("Cập nhật thành công");
                            this.DialogResult = DialogResult.OK;
                            this.Close();
                            Cnn.Close();
                        }
                        else
                        {
                            MessageBox.Show("Cập nhật thất bại ", "Thông báo", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
                        }


                    }

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Cnn.Close();
            }
        }
        private void btnCancle_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            if (MessageBox.Show("Thoát giao diện này?", "Thông báo", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
            {
                this.Close();
            }
        }
        private void gridView1_RowStyle(object sender, DevExpress.XtraGrid.Views.Grid.RowStyleEventArgs e)
        {
            GridView View = sender as GridView;
            if (e.RowHandle >= 0)
            {
                string sCheck = Convert.ToString(View.GetRowCellDisplayText(e.RowHandle, View.Columns["Note"]));
                if (sCheck == "Thiếu mã số sinh viên")
                {
                    e.Appearance.BackColor = Color.Salmon;

                }
                if (sCheck == "Sinh viên chưa có điểm")
                {
                    e.Appearance.BackColor = Color.Goldenrod;
                }
                if (sCheck == "Sinh viên không tồn tại")
                {
                    e.Appearance.BackColor = Color.PaleGreen;
                }

            }

        }
        private void cboClass_EditValueChanged(object sender, EventArgs e)
        {
            Endable();
            LoadSubject();
        }
        private void cboSubject_EditValueChanged(object sender, EventArgs e)
        {
            Endable();
        }



    }
}
